/***************************************************************
 *  Copyright (c) 2007 by GroupMe! Team (www.groupme.net)
 *  All rights reserved
 *
 *  This file is part of the GroupMe! Project. You can utilize
 *  software of the GroupMe! project under the terms of the 
 *  GNU Lesser General Public License as published by the
 *  Free Software Foundation in version 2.1.
 * 
 *  The GNU Lesser General Public License can be found at:
 *  http://www.gnu.org/licenses/lgpl.html
 *
 *  This copyright notice MUST APPEAR in all copies of the file!
 ***************************************************************/
package net.groupme.model.dao;

import java.io.UnsupportedEncodingException;
import java.net.URI;
import java.net.URISyntaxException;
import java.net.URLEncoder;
import java.sql.Date;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;

import javax.sql.DataSource;

import net.groupme.exceptions.DeleteFailedException;
import net.groupme.exceptions.InsertFailedException;
import net.groupme.exceptions.UpdateFailedException;
import net.groupme.gui.constants.VIEW_VOCABULARY;
import net.groupme.model.Group;
import net.groupme.model.Resource;
import net.groupme.model.User;
import net.groupme.utils.sql.GetResourcesQuery;
import net.groupme.utils.sql.GetStringFromTableWhereIDQuery;
import net.groupme.utils.sql.GetUsersQuery;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.SqlUpdate;
import org.springframework.jdbc.support.GeneratedKeyHolder;

/**
 * 
 * This class implements the {@link TagDAO} interface for a MySQL DB layer.  
 * 
 * @author Mischa Frank <a
 *         href="mailto:code@planetfrank.net">code@planetfrank.net</a>
 * @author Fabian Abel, <a href="mailto:abel@l3s.de">abel@l3s.de</a>
 * 
 * @author last edited by: $Author: patrick $
 * 
 * @version $Revision: 1.11 $ $Date: 2008-11-01 12:44:28 $
 */
public class TagDAOImpl implements TagDAO {


	@SuppressWarnings("unused")
	private static final Logger log = Logger.getLogger(TagDAOImpl.class);

	private DataSource ds;

	private String id = null;
	private String keyword = null;
	private String description = null;

	private String query;

	public TagDAOImpl(DataSource ds, String id) {
		this.ds = ds;
		this.id = id;
	}
	
	public TagDAOImpl(DataSource ds, String id, String keyword, String description) {
		this.ds = ds;
		this.id = id;
		this.keyword = keyword;
		this.description = description;
	}
	
	public TagDAOImpl(DataSource ds, String keyword, String description)
			throws InsertFailedException {
		this.ds = ds;
		SqlUpdate su = new SqlUpdate(ds, "INSERT INTO " + DATABASE_CONSTANTS.TABLE_TAG //$NON-NLS-1$
				+ " (" + DATABASE_CONSTANTS.TAG_KEYWORD + ", "
				+ DATABASE_CONSTANTS.TAG_DESCRIPTION + ") VALUES (?, ?)");
		su.declareParameter(new SqlParameter("keyword", Types.VARCHAR)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("description", Types.VARCHAR)); //$NON-NLS-1$
		su.compile();
		Object[] oa = new Object[2];
		oa[0] = new String(keyword);
		oa[1] = new String(description);
		GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
		int count = su.update(oa, keyHolder);
		if (count == 0)
			throw new InsertFailedException();
		this.id = keyHolder.getKey().toString();
		this.keyword = keyword;
		this.description = description;
	}

	/* (non-Javadoc)
	 * @see net.groupme.model.Identifiable#getId()
	 */
	public String getId() {
		return this.id;
	}
	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.Tag#add(net.groupme.model.Resource,
	 *      net.groupme.model.User, net.groupme.model.Group, java.util.Date,
	 *      double)
	 */
	public void add(Resource resource, User user, Group group, Date date,
			double importance) throws InsertFailedException {
		SqlUpdate su = new SqlUpdate(ds,
				"INSERT INTO " + DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE //$NON-NLS-1$
						+ " (" + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_USERID + ", "
						+ DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + ", "
						+ DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_GROUPID + ", "
						+ DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + ", "
						+ DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_DATE + ", "
						+ DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_IMPORTANCE
						+ ") VALUES (?, ?, ?, ?, ? ,?)");
		su.declareParameter(new SqlParameter("UserID", Types.INTEGER)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("TagID", Types.INTEGER)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("GroupID", Types.INTEGER)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("ResourceID", Types.INTEGER)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("Date", Types.DATE)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("Importance", Types.DOUBLE)); //$NON-NLS-1$
		su.compile();
		Object[] oa = new Object[6];
		oa[0] = user.getId();
		oa[1] = this.id;
		oa[2] = group.getId();
		oa[3] = resource.getId();
		oa[4] = date;
		oa[5] = importance;
		int count = su.update(oa);
		if (count == 0)
			throw new InsertFailedException();

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.Identifiable#getDescription()
	 */
	public String getDescription() {
		/*
		GetStringFromTableWhereIDQuery getDescriptionQuery = new GetStringFromTableWhereIDQuery(
				ds, DATABASE_CONSTANTS.TAG_DESCRIPTION, DATABASE_CONSTANTS.TABLE_TAG, id);
		Iterator iter = getDescriptionQuery.execute().iterator();
		if(iter.hasNext()){
			return (String) iter.next();
		}
		return null;
		*/
		return this.description;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.Tag#getKeyword()
	 */
	public String getKeyword() {
		if(this.keyword != null){
			return this.keyword;
		}
		GetStringFromTableWhereIDQuery getDescriptionQuery = new GetStringFromTableWhereIDQuery(
				ds, DATABASE_CONSTANTS.TAG_KEYWORD, DATABASE_CONSTANTS.TABLE_TAG, id);
		Iterator iter = getDescriptionQuery.execute().iterator();
		if(iter.hasNext()){
			return (String) iter.next();
		}
		return null;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.Identifiable#getLabel()
	 */
	public String getLabel() {
		return this.getKeyword();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.Tag#getResources()
	 */
	@SuppressWarnings("unchecked")
	public Collection<Resource> getResources() {
		query = "SELECT " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " FROM " + DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE + " WHERE " //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
				+ DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " = " + id; //$NON-NLS-1$
		GetResourcesQuery getResourcesQuery = new GetResourcesQuery(ds, query,
				DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID);
		Iterator iter = getResourcesQuery.execute().iterator();
		if(iter.hasNext()){
			return (Collection<Resource>) iter.next();
		}
		return new ArrayList<Resource>();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.Tag#getResources(net.groupme.model.User)
	 */
	@SuppressWarnings("unchecked")
	public Collection<Resource> getResources(User tagger) {
		query = "SELECT " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " FROM " + DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE + " WHERE " //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
				+ DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " = " + id + " AND "
				+ DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_USERID + " = "
				+ tagger.getId();
		GetResourcesQuery getResourcesQuery = new GetResourcesQuery(ds,
				query, DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID);
		return (Collection<Resource>)getResourcesQuery.execute().iterator().next();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.Tag#getResources(net.groupme.model.User,
	 *      net.groupme.model.Group)
	 */
	@SuppressWarnings("unchecked")
	public Collection<Resource> getResources(User tagger, Group group) {
		query = "SELECT " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " FROM " + DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE + " WHERE " //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
				+ DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " = " + id + " AND "
				+ DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_USERID + " = "
				+ tagger.getId() + " AND "
				+ DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_GROUPID + " = "
				+ group.getId();
		GetResourcesQuery getResourcesQuery = new GetResourcesQuery(ds,
				query, DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID);
		return (Collection<Resource>)getResourcesQuery.execute().iterator().next();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.Identifiable#getURI()
	 */
	public URI getURI() {
		if(keyword == null){
			keyword = getKeyword();
		}
		try {
			String URLkeyword = keyword.replaceAll(" ", "_");	
			URLkeyword = URLEncoder.encode(URLkeyword, "UTF-8");
			return new URI(VIEW_VOCABULARY.RESTful_TAG_URI_PATTERN + URLkeyword);//$NON-NLS-1$
		} catch (URISyntaxException e) {
			e.printStackTrace();
			return null;
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
			return null;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.Tag#remove(net.groupme.model.Resource,
	 *      net.groupme.model.User, net.groupme.model.Group)
	 */
	public void remove(Resource resource, User tagger, Group group)
			throws DeleteFailedException {
		SqlUpdate su;
		su = new SqlUpdate(
				ds,
				"DELETE FROM " + DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE //$NON-NLS-1$
						+ " WHERE " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_USERID + " = " + tagger.getId() //$NON-NLS-1$ //$NON-NLS-2$
						+ " AND " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " = " + this.id //$NON-NLS-1$ //$NON-NLS-2$
						+ " AND " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_GROUPID + " = " + group.getId() //$NON-NLS-1$ //$NON-NLS-2$
						+ " AND " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " = " + resource.getId()); //$NON-NLS-1$ //$NON-NLS-2$
		su.compile();
		int count = su.update();
		if (count == 0)
			throw new DeleteFailedException();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.Identifiable#setDescription(java.lang.String)
	 */
	public void setDescription(String description) throws UpdateFailedException {
		this.description = description;
		SqlUpdate su = new SqlUpdate(
				ds,
				"UPDATE " + DATABASE_CONSTANTS.TABLE_TAG + " SET " + DATABASE_CONSTANTS.TAG_DESCRIPTION + " = ? WHERE " + DATABASE_CONSTANTS.TAG_ID + " = ?"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
		su.declareParameter(new SqlParameter("description", Types.VARCHAR)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("ID", Types.INTEGER)); //$NON-NLS-1$
		su.compile();
		Object[] oa = new Object[2];
		oa[0] = description;
		oa[1] = new Integer(id);
		int count = su.update(oa);
		if (count == 0)
			throw new UpdateFailedException();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.Tag#setKeyword(java.lang.String)
	 */
	public void setKeyword(String keyword) throws UpdateFailedException {
		this.keyword = keyword;
		SqlUpdate su = new SqlUpdate(
				ds,
				"UPDATE " + DATABASE_CONSTANTS.TABLE_TAG + " SET " + DATABASE_CONSTANTS.TAG_KEYWORD + " = ? WHERE " + DATABASE_CONSTANTS.TAG_ID + " = ?"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
		su.declareParameter(new SqlParameter("keyword", Types.VARCHAR)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("ID", Types.INTEGER)); //$NON-NLS-1$
		su.compile();
		Object[] oa = new Object[2];
		oa[0] = keyword;
		oa[1] = new Integer(id);
		int count = su.update(oa);
		if (count == 0)
			throw new UpdateFailedException();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.Identifiable#setLabel(java.lang.String)
	 */
	public void setLabel(String label) {
		try {
			setKeyword(label);
		} catch (UpdateFailedException e) {
		}
	}

	/* (non-Javadoc)
	 * @see net.groupme.model.Tag#getUsers(net.groupme.model.Resource)
	 */
	@SuppressWarnings(value = "unchecked")
	public Collection<User> getUsers(Resource resource) {
		query = "SELECT " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_USERID + " FROM " + DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE + " WHERE " //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
		+ DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " = " + id + " AND "
		+ DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_USERID + " = " + resource.getId();
		
		GetUsersQuery getUserQuery = new GetUsersQuery(ds, query, DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_USERID);

		List userResult = getUserQuery.execute();
		if(userResult!= null && !userResult.isEmpty()){
			return (Collection<User>)userResult.iterator().next();
		}
		return new HashSet<User>();
	}

}
